package com.tjec.project.mdmSynchronous.dao.impl;

import com.tjec.common.utils.StringUtils;
import com.tjec.project.mdmSynchronous.dao.BpmRejectCommentDao;
import com.tjec.project.mdmSynchronous.domain.RejectComment;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author pangyongfeng
 */
@Service
public class BpmRejectCommentDaoImpl implements BpmRejectCommentDao {

	@Autowired
	private JdbcTemplate bpmPrimaryJdbcTemplate;

	@Override
	public List<Map<String, Object>> getRejectCommentList(RejectComment rejectComment) {
		String sql = "select oi.InstanceName,oc.ObjectID,oc.BizObjectSchemaCode,oc.UserName, "
				+ "case when oc.Text='' then '驳回' else oc.Text end as commentText,date_format(oc.CreatedTime, '%Y-%m-%d %H:%i:%s') CreatedTime,oc.InstanceId,ow.DisplayName "
				+ "from ot_comment oc LEFT JOIN ot_instancecontext oi on oc.InstanceId=oi.ObjectID left join ot_workitemfinished ow on ow.ObjectID=oc.WorkItemId "
				+ "where oc.Approval='0'";
		if (StringUtils.isNotBlank(rejectComment.getInstanceName())) {
			sql = sql + " and oi.instanceName like '%" + rejectComment.getInstanceName() + "%'";
		}
		if (StringUtils.isNotBlank(rejectComment.getDisplayName())) {
			sql = sql + " and ow.DisplayName like '%" + rejectComment.getDisplayName() + "%'";
		}
		if (StringUtils.isNotBlank(rejectComment.getCommentText())) {
			if ("驳回".equals(rejectComment.getCommentText())) {
				sql = sql + " and (oc.Text like '%" + rejectComment.getCommentText() + "%' or oc.Text='')";
			} else {
				sql = sql + " and oc.Text like '%" + rejectComment.getCommentText() + "%'";
			}
		}
		if (StringUtils.isNotBlank(rejectComment.getBizObjectSchemaCode())) {
			sql = sql + " and FIND_IN_SET(oc.BizObjectSchemaCode,'"+ rejectComment.getBizObjectSchemaCode() + "')" ;
		}
		if (StringUtils.isNotBlank(rejectComment.getUserName())) {
			sql = sql + " and oc.UserName like '%" + rejectComment.getUserName() + "%'";
		}
		if(StringUtils.isNotBlank(rejectComment.getBeginCreatedTime())) {
			sql = sql + " and date_format(oc.CreatedTime,'%y%m%d')>=date_format('"+rejectComment.getBeginCreatedTime()+"','%y%m%d')";
		}
		
		if(StringUtils.isNotBlank(rejectComment.getEndCreatedTime())) {
			sql = sql + " and date_format(oc.CreatedTime,'%y%m%d') <= date_format('"+rejectComment.getEndCreatedTime()+"','%y%m%d')";
		}
		sql = sql + " order by oc.CreatedTime desc limit " + rejectComment.getTotal() + ","
				+ rejectComment.getPageSize();

		List<Map<String, Object>> projectList = bpmPrimaryJdbcTemplate.queryForList(sql);
		return projectList;
	}

	@Override
	public List<Map<String, Object>> getRejectCommentCounts(RejectComment rejectComment) {
		String sql = "select count(*) counts "
				+ "from ot_comment oc LEFT JOIN ot_instancecontext oi on oc.InstanceId=oi.ObjectID left join ot_workitemfinished ow on ow.ObjectID=oc.WorkItemId "
				+ "where oc.Approval='0'";
		if (StringUtils.isNotBlank(rejectComment.getInstanceName())) {
			sql = sql + " and oi.instanceName like '%" + rejectComment.getInstanceName() + "%'";
		}
		if (StringUtils.isNotBlank(rejectComment.getDisplayName())) {
			sql = sql + " and ow.DisplayName like '%" + rejectComment.getDisplayName() + "%'";
		}
		if (StringUtils.isNotBlank(rejectComment.getCommentText())) {
			if ("驳回".equals(rejectComment.getCommentText())) {
				sql = sql + " and (oc.Text like '%" + rejectComment.getCommentText() + "%' or oc.Text='')";
			} else {
				sql = sql + " and oc.Text like '%" + rejectComment.getCommentText() + "%'";
			}
		}
		if (StringUtils.isNotBlank(rejectComment.getBizObjectSchemaCode())) {
			sql = sql + " and FIND_IN_SET(oc.BizObjectSchemaCode,'"+ rejectComment.getBizObjectSchemaCode() + "')" ;
		}
		if (StringUtils.isNotBlank(rejectComment.getUserName())) {
			sql = sql + " and oc.UserName like '%" + rejectComment.getUserName() + "%'";
		}
		if(StringUtils.isNotBlank(rejectComment.getBeginCreatedTime())) {
			sql = sql + " and date_format(oc.CreatedTime,'%y%m%d')>=date_format('"+rejectComment.getBeginCreatedTime()+"','%y%m%d')";
		}
		
		if(StringUtils.isNotBlank(rejectComment.getEndCreatedTime())) {
			sql = sql + " and date_format(oc.CreatedTime,'%y%m%d') <= date_format('"+rejectComment.getEndCreatedTime()+"','%y%m%d')";
		}

		List<Map<String, Object>> projectList = bpmPrimaryJdbcTemplate.queryForList(sql);
		return projectList;
	}

	@Override
	public List<Map<String, Object>> selectRejectComment(String createTime) {
		String sql = "select oi.InstanceName as instanceName,oc.ObjectID guid,oc.BizObjectSchemaCode as sheetCode,oc.UserName as rejectUser, "
				+ "case when oc.Text='' then '驳回' else oc.Text end as rejectContent,date_format(oc.CreatedTime, '%Y-%m-%d %H:%i:%s') as rejectTime,oc.InstanceId as instanceId,ow.DisplayName as rejectNode "
				+ "from ot_comment oc LEFT JOIN ot_instancecontext oi on oc.InstanceId=oi.ObjectID left join ot_workitemfinished ow on ow.ObjectID=oc.WorkItemId "
				+ "where oc.Approval='0'";
		if(StringUtils.isNotBlank(createTime)) {
			sql = sql + " and date_format(oc.CreatedTime,'%y%m%d')>date_format('"+createTime+"','%y%m%d')";
		}
		sql = sql + " order by oc.CreatedTime desc";
		List<Map<String, Object>> rejectList = bpmPrimaryJdbcTemplate.queryForList(sql);
		return rejectList;
	}
	
	
}
